package databaseConnect;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

public class AccessConnect {
	// Variables
	public static boolean is_connected = false;

	private static Connection con = null;

	public static boolean connect() {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

			con = DriverManager
					.getConnection("jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:/Users/Alex/Desktop/Diplomarbeit/Datenbank/database.mdb");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		is_connected = true;
		return true;
	}

	public static boolean disconnect() {
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		is_connected = false;
		return true;
	}

	public static Vector<Vector<String>> executeSelect(String statement) {
		Vector<Vector<String>> result = new Vector<Vector<String>>();
		try {
			Statement stmt = con.createStatement();
			//System.out.println(statement);
			ResultSet rs = stmt.executeQuery(statement);
			int clmCnt = rs.getMetaData().getColumnCount();
			Vector<String> row = null;
			while (rs.next()) {
				row = new Vector<String>();
				for (int i = 1; i <= clmCnt; i++) {
					row.add(rs.getString(i));
				}
				result.add(row);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	public static Vector<String> getColumnNamesForSelect(String statement) {
		Vector<String> result = new Vector<String>();
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(statement);
			ResultSetMetaData rsmd = rs.getMetaData();
			int clmCnt = rsmd.getColumnCount();
			for (int i = 1; i <= clmCnt; i++) {
				result.add(rsmd.getColumnName(i));
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	public static ResultSetMetaData getMetaDataForSelect(String statement) {
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(statement);
			return rs.getMetaData();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public static boolean executeInsert(String tablename,
			Vector<Vector<String>> data) {
		try {
			Statement stmt = con.createStatement();
			int indexOfLastElement = data.size();
			int counter = 0;
			for (int i = 0; i < data.size(); i++) {
				counter = counter + executeRowInsert(tablename, data.get(i));
			}
			stmt.close();
			return (indexOfLastElement == counter);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	// returns number of inserted rows
	public static int executeRowInsert(String tablename, Vector<String> data) {
		try {
			Statement stmt = con.createStatement();
			String statement = new String();
			int indexOfLastElement = data.size() - 1;
//			statement = statement + "INSERT INTO " + tablename + " VALUES (";
//			String current = null;
			for (int i = 0; i < data.size(); i++) {
//				current = data.get(i);
//				try {
//					int m = Integer.parseInt(current);
//					statement = statement + m;
//				} catch (NumberFormatException e) {
					statement = statement + "'" + data.get(i) + "'";
//				}
				if (i < indexOfLastElement) {
					statement = statement + ", ";
				}
			}
			statement = statement + ")";
			System.out.println(statement);
			if (stmt.executeUpdate(statement) == 1) {
				stmt.close();
				con.commit();
				return 1;
			}
			return 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}
	}

	// returns number of inserted rows
	public static boolean executeQuery(String query) {
		try {
			Statement stmt = con.createStatement();
//			System.out.println("QUERY:|" +query + "|ende");
			for (int i= 0 ; i < 200000; i++){
				//only for avoid write conflicts
			}
			if (stmt.execute(query)) {
				stmt.close();
				con.commit();
				return true;
			}
			return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	// public static void main(String[] args) throws Exception {
	//
	// if (connect()) {
	// executeSelect("SELECT * FROM TestTabelle");
	// }
	// disconnect();
	// }
}
